#!/bin/bash
/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute "
use online_edu_dws;
insert into online_edu_dws.dws_signup_info_table
with tmp as
(
select
    customer_id,
    area,
    anticipat_signup_date,
    appeal_status,
    create_date_time,
    deleted,
    origin_type,
    origin_channel,
    payment_state,
    payment_time,
    employee_id,
    tdepart_id,
    tdepart_name,
    class_id,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    "year",
    "month",
    "day"
from online_edu_dwb.dwb_signup_info_table
)
select
         case
             when grouping("year","month","day")=0
             then 'day'
             when grouping("year","month")=0
             then 'month'
             when grouping("year")=0
             then 'year'
         end as time_type,
        case
            when grouping(origin_type,itcast_school_id,itcast_subject_id)=0
            then 'type_school_subject'
            when grouping(origin_type,itcast_school_id)=0
            then 'type_school'
            when grouping(origin_type,itcast_subject_id)=0
            then 'type_subject'
            when grouping(origin_type,tdepart_id)=0
            then 'type_tdepart'
            when grouping(origin_type,origin_channel)=0
            then 'type_channel'
            when grouping(origin_type)=0
            then 'type'
            when grouping(itcast_school_id)=0
            then 'school'
        end as other_type ,
        count(distinct customer_id) as relationship_cnt,--意向人数
        sum(appeal_status) as appeal_cnt,--有效线索人数
        count(payment_state) as signup_cnt,--报名人数
        origin_type,
        origin_channel,
        tdepart_id,
        tdepart_name,
        itcast_school_id,
        itcast_school_name,
        itcast_subject_id,
        itcast_subject_name,
        "year",
        "month",
        "day"
from tmp
group by
grouping sets (
    --日
        ("year","month","day",itcast_school_id,itcast_school_name),--各个校区
        ("year","month","day",origin_type,itcast_school_id,itcast_school_name),--线上线下各个校区
        ("year","month","day",origin_type,itcast_subject_id,itcast_subject_name),--线上线下各个学科
        ("year","month","day",origin_type,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name),--线上线下各个校区各个学科
        ("year","month","day",origin_type,origin_channel),--线上线下各个来源渠道
        ("year","month","day",origin_type,tdepart_id,tdepart_name),--线上线下各个部门
        ("year","month","day",origin_type),
    --月
        ("year","month",itcast_school_id,itcast_school_name),
        ("year","month",origin_type,itcast_school_id,itcast_school_name),
        ("year","month",origin_type,itcast_subject_id,itcast_subject_name),
        ("year","month",origin_type,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name),
        ("year","month",origin_type,origin_channel),
        ("year","month",origin_type,tdepart_id,tdepart_name),
        ("year","month",origin_type),
    --年
        ("year",itcast_school_id,itcast_school_name),
        ("year",origin_type,itcast_school_id,itcast_school_name),
        ("year",origin_type,itcast_subject_id,itcast_subject_name),
        ("year",origin_type,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name),
        ("year",origin_type,origin_channel),
        ("year",origin_type,tdepart_id,tdepart_name),
        ("year",origin_type)
)
order by time_type,other_type;
"